﻿Imports System
Imports System.Data.OleDb

Public Class DAL_GiaoVien
    Public conn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\10650_000\Desktop\ltudql1-doan\Database\Database.mdb"

#Region "  Quan lý ngan hang cau hỏi  ***************************************************"



    Public Function LoadNganHangCauHoi() As DataTable

        Dim StrSQL As String

        StrSQL = "Select * from CAUHOI"

        Dim con As New OleDbConnection(conn)

        Dim da As New OleDbDataAdapter(StrSQL, con)

        Dim dt As New DataTable

        da.Fill(dt)

        Return dt

    End Function

    Public Function ThongKeCauHoiTheoMucDo(ByVal mucdo As String) As DataTable

        Dim dt As New DataTable

        Dim StrSQL As String = "Select * from CauHoi where mucdo = '" & mucdo & "' "

        Dim con As New OleDbConnection(conn)

        Dim da As New OleDbDataAdapter(StrSQL, con)

        da.Fill(dt)

        Return dt

    End Function

    Public Sub ThemCauHoi(ByVal macauhoi As String, ByVal noidung As String, ByVal mucdo As String, ByVal daa As String, _
    ByVal dab As String, ByVal dac As String, ByVal dad As String, ByVal dadung As String)

        Dim strSQL As String

        strSQL = "Insert into CAUHOI(macauhoi,noidung,mucdo,daa,dab,dac,dad,dadung) values(@macauhoi,@noidung,@mucdo,@daa,@dab,@dac,@dad,@dadung)"

        Dim con As New OleDbConnection(conn)

        Dim cmd As New OleDbCommand(strSQL, con)

        With cmd.Parameters

            .Add("@macauhoi", OleDbType.VarChar).Value = macauhoi

            .Add("@noidung", OleDbType.VarChar).Value = noidung

            .Add("@mucdo", OleDbType.VarChar).Value = mucdo

            .Add("@daa", OleDbType.VarChar).Value = daa

            .Add("@dab", OleDbType.VarChar).Value = dab

            .Add("@dac", OleDbType.VarChar).Value = dac

            .Add("@dad", OleDbType.VarChar).Value = dad

            .Add("@dadung", OleDbType.VarChar).Value = dadung

        End With

        con.Open()

        cmd.ExecuteNonQuery()

        con.Close()

    End Sub

    Public Sub SuaCauHoi(ByVal macauhoi As String, ByVal noidung As String, ByVal mucdo As String, ByVal DAA As String, _
   ByVal DAB As String, ByVal DAC As String, ByVal DAD As String, ByVal DADUNG As String)

        Dim strSQL As String

        strSQL = " Update CAUHOI set noidung = @noidung,mucdo =@mucdo ,daa = @daa ,dab = @dab,dac = @dac ,dad =@dad ,dadung =@dadung   where( macauhoi=@macauhoi)"

        Dim con As New OleDbConnection(conn)

        Dim cmd As New OleDbCommand(strSQL, con)

        With cmd.Parameters

            .Add("@noidung", OleDbType.VarChar).Value = noidung

            .Add("@mucdo", OleDbType.VarChar).Value = mucdo

            .Add("@daa", OleDbType.VarChar).Value = DAA

            .Add("@dab", OleDbType.VarChar).Value = DAB

            .Add("@dac", OleDbType.VarChar).Value = DAC

            .Add("@dad", OleDbType.VarChar).Value = DAD

            .Add("@dadung", OleDbType.VarChar).Value = DADUNG

            .Add("@macauhoi", OleDbType.VarChar).Value = macauhoi

        End With

        con.Open()

        cmd.ExecuteNonQuery()

        con.Close()

    End Sub

    Public Sub XoaCauHoi(ByVal macauhoi As String)

        Dim strSQL As String

        strSQL = "delete * from CauHoi where Macauhoi=@Macauhoi"

        Dim con As New OleDbConnection(conn)

        Dim cmd As New OleDbCommand(strSQL, con)

        cmd.Parameters.Add("@macauhoi", OleDbType.VarChar).Value = macauhoi

        con.Open()

        cmd.ExecuteNonQuery()

        con.Close()

    End Sub

    Public Function LayMaCauHoiLonNhat() As DataTable

        Dim StrSQL As String

        StrSQL = "SELECT TOP 1 MaCauHoi FROM CAUHOI ORDER BY MaCauHoi DESC "

        Dim con As New OleDbConnection(conn)

        Dim da As New OleDbDataAdapter(StrSQL, con)

        Dim dt As New DataTable

        da.Fill(dt)

        Return dt

    End Function

#End Region

#Region "Quan ly bo de ************************************************************************"




    Public Function LayMaBoDeLonNhat() As DataTable

        Dim StrSQL As String

        StrSQL = "SELECT TOP 1 MaBoDe FROM BODE ORDER BY MABODE DESC "

        Dim con As New OleDbConnection(conn)

        Dim da As New OleDbDataAdapter(StrSQL, con)

        Dim dt As New DataTable

        da.Fill(dt)

        Return dt

    End Function

    Public Sub XoaBoDeRaKhoiDanhSach(ByVal mabode As String)

        Dim StrSQL As String

        StrSQL = "Delete * From BODE where mabode = @mabode"

        Dim con As New OleDbConnection(conn)

        Dim cmd As New OleDbCommand(StrSQL, con)

        cmd.Parameters.Add("@mabode", OleDbType.VarChar).Value = mabode

        con.Open()

        cmd.ExecuteNonQuery()

        con.Close()

    End Sub

    Public Sub ThemBoDe(ByVal mabode As String, ByVal tenbode As String, ByVal soluong As String, ByVal thoigianthi As String)

        Dim strSQL As String

        strSQL = "Insert into bode (mabode,tenbode,soluong,thoigianthi) values(@mabode,@tenbode,@soluong,@thoigianthi)"

        Dim con As New OleDbConnection(conn)

        Dim cmd As New OleDbCommand(strSQL, con)

        With cmd.Parameters

            .Add("@mabode", OleDbType.VarChar).Value = mabode

            .Add("@tenbode", OleDbType.VarChar).Value = tenbode

            .Add("@soluong", OleDbType.VarChar).Value = soluong

            .Add("@thoigianthii", OleDbType.VarChar).Value = thoigianthi

        End With

        con.Open()

        cmd.ExecuteNonQuery()

        con.Close()

    End Sub

    Public Function LoadDanhSachBoDe() As DataTable

        Dim dt As New DataTable

        Dim StrSQL As String = "Select * from BODE "

        Dim con As New OleDbConnection(conn)

        Dim da As New OleDbDataAdapter(StrSQL, con)

        da.Fill(dt)

        Return dt

    End Function

    Public Sub SuaBoDe(ByVal tenbode As String, ByVal soluong As String, ByVal thoigianthi As String, ByVal mabode As String)

        Dim strSQL As String

        strSQL = "update bode set tenbode=@tenbode,soluong=@soluong,thoigianthi=@thoigianthi where mabode=@mabode "

        Dim con As New OleDbConnection(conn)

        Dim cmd As New OleDbCommand(strSQL, con)

        With cmd.Parameters

            .Add("@tenbode", OleDbType.VarChar).Value = tenbode

            .Add("@soluong", OleDbType.VarChar).Value = soluong

            .Add("@thoigianthii", OleDbType.VarChar).Value = thoigianthi

            .Add("@mabode", OleDbType.VarChar).Value = mabode

        End With

        con.Open()

        cmd.ExecuteNonQuery()

        con.Close()

    End Sub

    Public Sub AddVaoBangCauHoiChoBoDe(ByVal MaBoDe As String, ByVal MaCauHoi As String)

        Dim strSQL As String

        strSQL = "Insert into CAUHOICHOBODE(MaBoDe,MaCauHoi) values(@MaBoDe,@MaCauHoi)"

        Dim con As New OleDbConnection(conn)

        Dim cmd As New OleDbCommand(strSQL, con)

        cmd.Parameters.Add("@MaBoDe", OleDbType.VarChar).Value = MaBoDe

        cmd.Parameters.Add("@MaCauHoi", OleDbType.VarChar).Value = MaCauHoi

        con.Open()

        cmd.ExecuteNonQuery()

        con.Close()

    End Sub

    Public Function LoadCauHoiChoBoDeKhiThemTuDong(ByVal mabode As String) As DataTable

        Dim StrSQL As String

        StrSQL = "SELECT * from cauhoichobode where mabode = '" & mabode & "' "

        Dim con As New OleDbConnection(conn)

        Dim da As New OleDbDataAdapter(StrSQL, con)

        Dim dt As New DataTable

        da.Fill(dt)

        Return dt

    End Function

#End Region

#Region " Quan ly tai khoan giao vien va tai khoan sinh vien ********************************************"

    Public Function LoadDanhSachTaiKhoan() As DataTable

        Dim StrSQL As String

        StrSQL = "Select * from TAIKHOAN"

        Dim con As New OleDbConnection(conn)

        Dim da As New OleDbDataAdapter(StrSQL, con)

        Dim dt As New DataTable

        da.Fill(dt)

        Return dt

    End Function

    Public Sub ThemSinhVienMoi(ByVal masv As String, ByVal tensv As String, ByVal gioitinh As String, ByVal ngaysinh As String, _
    ByVal dienthoai As String, ByVal malop As String)

        Dim strSQL As String

        strSQL = "Insert into SINHVIEN(masv,tensv ,gioitinh,ngaysinh ,dienthoai,malop) values(@masv,@tensv ,@gioitinh,@ngaysinh ,@dienthoai,@malop)"

        Dim con As New OleDbConnection(conn)

        Dim cmd As New OleDbCommand(strSQL, con)

        With cmd.Parameters

            .Add("@masv", OleDbType.VarChar).Value = masv

            .Add("@tensv", OleDbType.VarChar).Value = tensv

            .Add("@gioitinh", OleDbType.VarChar).Value = gioitinh

            .Add("@ngaysinh", OleDbType.VarChar).Value = ngaysinh

            .Add("@dienthoai", OleDbType.VarChar).Value = dienthoai

            .Add("@malop", OleDbType.VarChar).Value = malop

        End With

        con.Open()

        cmd.ExecuteNonQuery()

        con.Close()

    End Sub

    Public Sub Suathongtinsinhvien(ByVal tensv As String, ByVal gioitinh As String, ByVal ngaysinh As String, _
   ByVal dienthoai As String, ByVal malop As String, ByVal masv As String)

        Dim strSQL As String

        strSQL = "UPDATE SINHVIEN set  tensv = @tensv ,gioitinh = @gioitinh,ngaysinh = @ngaysinh ,dienthoai = @dienthoai,malop = @malop where masv = @masv "

        Dim con As New OleDbConnection(conn)

        Dim cmd As New OleDbCommand(strSQL, con)

        With cmd.Parameters

            .Add("@tensv", OleDbType.VarChar).Value = tensv

            .Add("@gioitinh", OleDbType.VarChar).Value = gioitinh

            .Add("@ngaysinh", OleDbType.VarChar).Value = ngaysinh

            .Add("@dienthoai", OleDbType.VarChar).Value = dienthoai

            .Add("@malop", OleDbType.VarChar).Value = malop

            .Add("@masv", OleDbType.VarChar).Value = masv

        End With

        con.Open()

        cmd.ExecuteNonQuery()

        con.Close()

    End Sub

    Public Function XoaSinhVien(ByVal MaSV As String) As DataTable

        Dim StrSQL As String

        StrSQL = "Delete * from SINHVIEN where masv  = '" & MaSV & "' "

        Dim con As New OleDbConnection(conn)

        Dim da As New OleDbDataAdapter(StrSQL, con)

        Dim dt As New DataTable

        da.Fill(dt)

        Return dt

    End Function

    Public Function LayMaTaiKhoanLonNhat() As DataTable

        Dim StrSQL As String

        StrSQL = "SELECT TOP 1 MaTAIKHOAN FROM TAIKHOAN ORDER BY MaTAIKHOAN DESC "

        Dim con As New OleDbConnection(conn)

        Dim da As New OleDbDataAdapter(StrSQL, con)

        Dim dt As New DataTable

        da.Fill(dt)

        Return dt

    End Function

    Public Function LayMaSinhVienLonNhat() As DataTable

        Dim StrSQL As String

        StrSQL = "SELECT TOP 1 MaSV FROM SINHVIEN ORDER BY MaSV DESC "

        Dim con As New OleDbConnection(conn)

        Dim da As New OleDbDataAdapter(StrSQL, con)

        Dim dt As New DataTable

        da.Fill(dt)

        Return dt

    End Function

    Public Function LocSinhVienTheoMaLop(ByVal malop As String) As DataTable

        Dim StrSQL As String

        StrSQL = "SELECT *  FROM SINHVIEN Where malop = '" & malop & "' "

        Dim con As New OleDbConnection(conn)

        Dim da As New OleDbDataAdapter(StrSQL, con)

        Dim dt As New DataTable

        da.Fill(dt)

        Return dt

    End Function

    Public Sub ThemTaiKhoanMoi(ByVal MATAIKHOAN As String, ByVal TENTAIKHOAN As String, ByVal MATKHAU As String)

        Dim strSQL As String

        strSQL = "Insert into TAIKHOAN(MATAIKHOAN,TENTAIKHOAN,MATKHAU) values(@MATAIKHOAN,@TENTAIKHOAN,@MATKHAU)"

        Dim con As New OleDbConnection(conn)

        Dim cmd As New OleDbCommand(strSQL, con)

        With cmd.Parameters

            .Add("@MATAIKHOAN", OleDbType.VarChar).Value = MATAIKHOAN

            .Add("@TENTAIKHOAN", OleDbType.VarChar).Value = TENTAIKHOAN

            .Add("@MATKHAU", OleDbType.VarChar).Value = MATKHAU

        End With

        con.Open()

        cmd.ExecuteNonQuery()

        con.Close()

    End Sub

    Public Function XoaTaiKhoan(ByVal MATAIKHOAN As String) As DataTable

        Dim StrSQL As String

        StrSQL = "Delete * from TAIKHOAN where MATAIKHOAN = '" & MATAIKHOAN & "' "

        Dim con As New OleDbConnection(conn)

        Dim da As New OleDbDataAdapter(StrSQL, con)

        Dim dt As New DataTable

        da.Fill(dt)

        Return dt

    End Function

    Public Sub SuaTaiKhoanQuanTri(ByVal tentaikhoan As String, ByVal matkhau As String, ByVal mataikhoan As String)

        Dim StrSQL As String

        StrSQL = "Update TAIKHOAN set tentaikhoan = @tentaikhoan, matkhau = @matkhau where mataikhoan = @mataikhoan"

        Dim con As New OleDbConnection(conn)

        Dim cmd As New OleDbCommand(StrSQL, con)

        With cmd.Parameters

            .Add("@tentaikhoan", OleDbType.VarChar).Value = tentaikhoan

            .Add("@matkhau", OleDbType.VarChar).Value = matkhau

            .Add("@mataikhoan", OleDbType.VarChar).Value = mataikhoan

        End With

        con.Open()

        cmd.ExecuteNonQuery()

        con.Close()

    End Sub

#End Region

#Region " Quan ly thi tape tong hop ************************************************************** "



    Public Function XoaDanhSachSVDa_THI() As DataTable

        Dim dt As New DataTable

        Dim StrSQL As String = "delete * from SINHVIEN_THI "

        Dim con As New OleDbConnection(conn)

        Dim da As New OleDbDataAdapter(StrSQL, con)

        da.Fill(dt)

        Return dt

    End Function

    Public Function XoaTungSVtrongDanhSachSV_THI(ByVal masv As String) As DataTable

        Dim StrSQL As String = "delete * from SINHVIEN_THI where masv = '" & masv & "' "

        Dim con As New OleDbConnection(conn)

        Dim da As New OleDbDataAdapter(StrSQL, con)

        Dim dt As New DataTable

        da.Fill(dt)

        Return dt

    End Function

    Public Function LoadDanhSachSinhVien() As DataTable

        Dim StrSQL As String

        StrSQL = "Select * from SINHVIEN order by masv asc"

        Dim con As New OleDbConnection(conn)

        Dim da As New OleDbDataAdapter(StrSQL, con)

        Dim dt As New DataTable

        da.Fill(dt)

        Return dt

    End Function

    Public Function LoadDanhSachSinhVien_THI() As DataTable

        Dim StrSQL As String

        StrSQL = "Select * from SINHVIEN_THI"

        Dim con As New OleDbConnection(conn)

        Dim da As New OleDbDataAdapter(StrSQL, con)

        Dim dt As New DataTable

        da.Fill(dt)

        Return dt

    End Function

    Public Sub ThemSinhVienVaoDanhSach_Thi(ByVal masv As String, ByVal tensv As String, ByVal mabode As String, ByVal tongsocauhoi As String, ByVal thoigian As String)

        Dim strSQL As String

        strSQL = "Insert into SINHVIEN_THI(masv,tensv,mabode,tongsocauhoi,thoigian) values(@masv,@tensv,@mabode,@tongsocauhoi,@thoigian)"

        Dim con As New OleDbConnection(conn)

        Dim cmd As New OleDbCommand(strSQL, con)

        With cmd.Parameters

            .Add("@masv", OleDbType.VarChar).Value = masv

            .Add("@tensv", OleDbType.VarChar).Value = tensv

            .Add("@mabode", OleDbType.VarChar).Value = mabode

            .Add("@tongsocauhoi", OleDbType.VarChar).Value = tongsocauhoi

            .Add("@thoigian", OleDbType.VarChar).Value = thoigian

        End With

        con.Open()

        cmd.ExecuteNonQuery()

        con.Close()


    End Sub

    Public Function LoadDuLieuChoComboBoxLop() As DataTable

        Dim dt As New DataTable

        Dim StrSQl As String = "Select malop from Lop group by malop"

        Dim con As New OleDbConnection(conn)

        Dim da As New OleDbDataAdapter(StrSQl, con)

        da.Fill(dt)

        Return dt

    End Function

    Public Function LocSinhVienTheoLop(ByVal malop As String) As DataTable

        Dim dt As New DataTable

        Dim StrSQL As String

        StrSQL = "SELECT SINHVIEN.MASV, SINHVIEN.TENSV   FROM(SINHVIEN) WHERE (SINHVIEN.MaLop)= '" & malop & "'"

        Dim con As New OleDbConnection(conn)

        Dim da As New OleDbDataAdapter(StrSQL, con)

        da.Fill(dt)

        Return dt

    End Function

    Public Sub ThemSinhVienVaoDanhSach_Thi(ByVal MALOP As String, ByVal TENLOP As String)

        Dim strSQL As String

        strSQL = "Insert into Lop(MALOP,TENLOP) values(@MALOP,@TENLOP)"

        Dim con As New OleDbConnection(conn)

        Dim cmd As New OleDbCommand(strSQL, con)

        With cmd.Parameters

            .Add("@MALOP", OleDbType.VarChar).Value = MALOP

            .Add("@TENLOP", OleDbType.VarChar).Value = TENLOP

        End With

        con.Open()

        cmd.ExecuteNonQuery()

        con.Close()

    End Sub

#End Region

#Region " Quản lý lơp *************************************************************************"

    Public Function LoadDanhSachLop() As DataTable

        Dim StrSQL As String

        StrSQL = "Select * from LOP"

        Dim con As New OleDbConnection(conn)

        Dim da As New OleDbDataAdapter(StrSQL, con)

        Dim dt As New DataTable

        da.Fill(dt)

        Return dt

    End Function

    Public Function NhomSinhVienTheoLop(ByVal malop As String) As DataTable

        Dim StrSQL As String

        StrSQL = "SELECT * from SINHVIEN where malop  = '" & malop & "' "

        Dim con As New OleDbConnection(conn)

        Dim da As New OleDbDataAdapter(StrSQL, con)

        Dim dt As New DataTable

        da.Fill(dt)

        Return dt

    End Function

    Public Sub ThemLopMoi(ByVal MALOP As String, ByVal TENLOP As String)

        Dim strSQL As String

        strSQL = "Insert into Lop(MALOP,TENLOP) values(@MALOP,@TENLOP)"

        Dim con As New OleDbConnection(conn)

        Dim cmd As New OleDbCommand(strSQL, con)

        With cmd.Parameters

            .Add("@MALOP", OleDbType.VarChar).Value = MALOP

            .Add("@TENLOP", OleDbType.VarChar).Value = TENLOP

        End With

        con.Open()

        cmd.ExecuteNonQuery()

        con.Close()

    End Sub

    Public Sub SuaThongTinLop(ByVal TENLOP As String, ByVal MALOP As String)

        Dim strSQL As String

        strSQL = " Update LOP set TENLOP = @TENLOP where malop = @malop "

        Dim con As New OleDbConnection(conn)

        Dim cmd As New OleDbCommand(strSQL, con)

        With cmd.Parameters

            .Add("@TENLOP", OleDbType.VarChar).Value = TENLOP

            .Add("@MALOP", OleDbType.VarChar).Value = MALOP

        End With

        con.Open()

        cmd.ExecuteNonQuery()

        con.Close()

    End Sub

    Public Function XoaLopDangChon(ByVal malop As String) As DataTable

        Dim strSQL As String

        strSQL = " Delete * from Lop where malop = '" & malop & "' "

        Dim con As New OleDbConnection(conn)

        Dim da As New OleDbDataAdapter(strSQL, con)

        Dim dt As New DataTable

        da.Fill(dt)

        Return dt

    End Function

#End Region

#Region "Xem diem va tim kiem diem thi cua sinh vien da thi ******************************** "


    Public Function Xemthongtinsinhvienthivadiem() As DataTable

        Dim strSQL As String

        strSQL = " Select * from CTSVTHI "

        Dim con As New OleDbConnection(conn)

        Dim da As New OleDbDataAdapter(strSQL, con)

        Dim dt As New DataTable

        da.Fill(dt)

        Return dt

    End Function

    Public Function TimKiemThongTinSinhVienVaDiem(ByVal masv As String) As DataTable

        Dim StrSQl As String

        StrSQl = "Select * from CTSVTHI where (masv = '" & masv & "')"

        Dim con As New OleDbConnection(conn)

        Dim da As New OleDbDataAdapter(StrSQl, con)

        Dim dt As New DataTable

        da.Fill(dt)

        Return dt

    End Function

    Public Function Xemdanhsachsinhviendathitheongay(ByVal ngaythi As String) As DataTable

        Dim dt As New DataTable

        Dim StrSQL As String = "Select * from CTSVTHI where ngaythi = '" & ngaythi & "'"

        Dim con As New OleDbConnection(conn)

        Dim da As New OleDbDataAdapter(StrSQL, con)

        da.Fill(dt)

        Return dt

    End Function

#End Region

End Class
